<?php

/* ========================================================================= */
/* This file is part of the HypnoPHP Framework Library                       */
/* Copyright (c) 2009-2010, Radu Buzea                                       */
/* ========================================================================= */

/**
 @author Radu Buzea
 @copyright Copyright (c) 2009-2010, Radu Buzea (www.hypnosis.ro)
 @license GNU Lesser General Public License (www.gnu.org/copyleft/lesser.html)
 @package MySQL Module for HypnoPHP
*/

// ============================================================================
// Class: Module_MySQL_Query
// ============================================================================

class Module_MySQL_Query extends HBaseDbQuery
{

	/**
	* MySQL logical operators
	*
	* @var array Predefined list of MySQL logical operators
	*/
	protected $__where_operators = array(
		'=', '>', '<', '<', '<=', '=>', '<=>', '<>', '!=', 'IS', 'IS NOT', 'LIKE',
	);

	# ###########################################################################

	/**
	* Internal method for compiling SQL SELECT statements
	*
	* @param string $qkey
	* @return string SQL
	*/
	protected function __compile_select($qkey = 'SELECT')
	{
		if (!$this->isReady()) { return false; }
		if ($qkey != 'SELECT' && strstr($qkey, 'UNION_SELECT_') === false) { return false; }
		if (!isset($this->__query[$qkey])) { return false; }
		$data = $this->__query[$qkey];
		// Check for DISTINCT statement
		if (isset($data['DISTINCT']) && $data['DISTINCT']) { $sql = "SELECT DISTINCT "; }
		else { $sql = "SELECT "; }
		// Check for SELECT FIELDS sub-section
		if (!isset($data['FIELDS'])) {
			$this->addError("There is no FIELDS sub-section in the SELECT query section",
				E_USER_WARNING);
			return false;
		}
		// Check for SELECT FROM sub-section
		if (!isset($data['FROM']) || !is_array($data['FROM']) || count($data['FROM']) == 0) {
			$this->addError("There is no FROM sub-section in the SELECT query section",
				E_USER_WARNING);
			return false;
		}
		// Adding FIELDS into the SQL string
		$i = 0;
		foreach ($data['FIELDS'] as $field => $as_name) {
			if ($i > 0) { $sql .= ", "; }
			$sql .= $this->database_object->escapeNames($field);
			if ($as_name != '') { $sql .= " AS " . $this->database_object->escapeNames($as_name); }
			$i++;
		}

		$sql .= $this->__compile_from($qkey); // Compile the SELECT FROM sub-sections
		$sql_join = $this->__compile_join($qkey); // Compile the SELECT JOIN sub-sections (if any)
		$sql_where = $this->__compile_where_having(0, $qkey); // Compile the SELECT WHERE sub-section (if any)
		$sql_having = $this->__compile_where_having(1, $qkey); // Compile the SELECT HAVING sub-section (if any)
		$sql_order = $this->__compile_order_group(0, $qkey); // Compile the SELECT ORDER sub-section (if any)
		$sql_group = $this->__compile_order_group(1, $qkey); // Compile the SELECT GROUP sub-section (if any)

		// Adding compiled JOIN statement into the SQL string
		if ($sql_join !== false && is_string($sql_join) && $sql_join != '') {
			$sql .= $sql_join;
		}
		// Adding compiled WHERE statement into the SQL string
		if ($sql_where !== false && is_string($sql_where) && $sql_where != '') {
			$sql .= $sql_where;
		}
		// Adding compiled GROUP BY statement into the SQL string
		if ($sql_group !== false && is_string($sql_group) && $sql_group != '') {
			$sql .= $sql_group;
		}
		// Adding compiled HAVING statement into the SQL string
		if ($sql_having !== false && is_string($sql_having) && $sql_having != '') {
			$sql .= $sql_having;
		}
		// Adding compiled ORDER BY statement into the SQL string
		if ($sql_order !== false && is_string($sql_order) && $sql_order != '') {
			$sql .= $sql_order;
		}
		// Compile the DELETE LIMIT sub-section (if any)
		$sql .= $this->__compile_limit($qkey);
		return $sql;
	}

	# ###########################################################################

	/**
	* Internal method for compiling SQL INSERT statements
	*
	* @return string SQL
	*/
	protected function __compile_insert()
	{
		if (!$this->isReady()) { return false; }
		$qkey = $this->__query_key;
		if ($qkey != 'INSERT') { return false; }
		if (!isset($this->__query[$qkey])) { return false; }
		$data = $this->__query[$qkey];
		// Check for FIELDS and TABLE sub-sections
		if (!isset($data['FIELDS']) || !is_array($data['FIELDS']) || count($data['FIELDS']) == 0) {
			return false;
		}
		if (!isset($data['TABLE']) || !is_string($data['TABLE']) || trim($data['TABLE']) == '') {
			return false;
		}
		$fields = '';
		$values = '';
		// Compile fields values
		foreach ($data['FIELDS'] as $field => $value) {
			if ($fields != '') { $fields .= ', '; }
			if ($values != '') { $values .= ', '; }
			$fields .= $this->database_object->escapeNames($field);
			$values .= "'" . $this->database_object->escapeStr($value) . "'";
		}
		$sql = "INSERT INTO " . $this->database_object->escapeNames($data['TABLE']) .
			" \n($fields) \nVALUES \n($values)";
		return $sql;
	}

	# ###########################################################################

	/**
	* Internal method for compiling SQL UPDATE statements
	*
	* @return string SQL
	*/
	protected function __compile_update()
	{
		if (!$this->isReady()) { return false; }
		$qkey = $this->__query_key;
		if ($qkey != 'UPDATE') { return false; }
		if (!isset($this->__query[$qkey])) { return false; }
		$data = $this->__query[$qkey];
		// Check for FIELDS and TABLE sub-sections
		if (!isset($data['FIELDS']) || !is_array($data['FIELDS']) || count($data['FIELDS']) == 0) {
			return false;
		}
		if (!isset($data['TABLE']) || !is_string($data['TABLE']) || trim($data['TABLE']) == '') {
			return false;
		}
		$update = '';
		// Compile fields updates
		foreach ($data['FIELDS'] as $field => $value) {
			if ($update != '') { $update .= ","; }
			$update .= "\n " . $this->database_object->escapeNames($field) . " = '" .
				$this->database_object->escapeStr($value) . "'";
		}
		$sql = "UPDATE " . $this->database_object->escapeNames($data['TABLE']) . " SET $update";
		// Compile the UPDATE WHERE sub-section (if any) and
		// adding compiled WHERE statement into the SQL string
		$sql_where = $this->__compile_where_having(0, $qkey);
		if ($sql_where !== false && is_string($sql_where) && $sql_where != '') {
			$sql .= $sql_where;
		}
		// Compile the DELETE LIMIT sub-section (if any)
		$sql .= $this->__compile_limit($qkey);
		return $sql;
	}

	# ###########################################################################

	/**
	* Internal method for compiling SQL DELETE statements
	*
	* @return string SQL
	*/
	protected function __compile_delete()
	{
		$qkey = $this->__query_key;
		if ($qkey != 'DELETE') { return false; }
		if (!isset($this->__query[$qkey])) { return false; }
		$data = $this->__query[$qkey];
		if (!isset($data['FROM'])) { return false; }
		$sql = "DELETE " . $this->__compile_from($qkey);
		// Compile the DELETE WHERE sub-section (if any) and
		// adding compiled WHERE statement into the SQL string
		$sql_where = $this->__compile_where_having(0, $qkey);
		if ($sql_where !== false && is_string($sql_where) && $sql_where != '') {
			$sql .= $sql_where;
		}
		// Compile the DELETE LIMIT sub-section (if any)
		$sql .= $this->__compile_limit($qkey);
		return $sql;
	}

	# ###########################################################################

	/**
	* Internal method for compiling SQL FROM statements
	*
	* @param string $qkey
	* @return string SQL
	*/
	protected function __compile_from($qkey = null)
	{
		if (!$this->isReady()) { return false; }
		// Get the query base statement
		if ($qkey == null) { $qkey = $this->__query_key; }
		if (!isset($this->__query[$qkey])) { return ''; }
		if (!isset($this->__query[$qkey]["FROM"])) { return ''; }
		$data = $this->__query[$qkey]["FROM"];
		// Adding FROM into the SQL string
		$sql = "FROM ";
		foreach ($data as $key => $from) {
			if (isset($from['TABLE'])) {
				if ($key > 0) { $sql .= ", "; }
				$sql .= $this->database_object->escapeNames($from['TABLE']);
				if (isset($from['AS']) && $from['AS'] != '') {
					$sql .= " AS " . $this->database_object->escapeNames($from['AS']);
				}
			}
		}
		return " \n" . $sql;
	}

	# ###########################################################################

	/**
	* Internal method for compiling SQL JOIN statements
	*
	* @param string $qkey
	* @return string SQL
	*/
	protected function __compile_join($qkey = null)
	{
		if (!$this->isReady()) { return false; }
		if ($qkey == null) { $qkey = $this->__query_key; }
		if (!isset($this->__query[$qkey])) { return ''; }
		$data = $this->__query[$qkey];
		$sql = '';
		foreach ($this->__join_keys as $join_key) {
			if (isset($data[$join_key]) && isset($data[$join_key][0])) {
				$join_data = $data[$join_key];
				$join_sql = str_replace('_', ' ', $join_key);
				foreach ($join_data as $join_part) {
					if (isset($join_part['TABLE'])) {
						if (isset($join_part['OUTER'])) {
							$sql .= " \n" . str_replace(' ', ' OUTER ', $join_sql) . " " .
								$this->database_object->escapeNames($join_part['TABLE']);
						} else {
							$sql .= " \n" . $join_sql . " " .
								$this->database_object->escapeNames($join_part['TABLE']);
						}
						if (isset($join_part['AS'])) {
							$sql .= " AS " . $this->database_object->escapeNames($join_part['AS']);
						}
						if (isset($join_part['CONDITION'])) {
							$sql .= " ON " . $join_part['CONDITION'];
						}
					}
				}
			}
		}
		return $sql;
	}

	# ###########################################################################

	/**
	* Internal method for compiling SQL WHERE and HAVING statements
	*
	* @param integer $mode 0 (zero) for WHERE, anything else for HAVING
	* @param string $qkey
	* @return string SQL
	*/
	protected function __compile_where_having($mode = 0, $qkey = null)
	{
		if (!$this->isReady()) { return false; }
		// Get the query base statement
		if ($qkey == null) { $qkey = $this->__query_key; }
		if (!isset($this->__query[$qkey])) { return ''; }
		$pkey = ($mode == 0) ? 'WHERE' : 'HAVING';
		if (isset($this->__query[$qkey]['QUICK_' . $pkey])) {
			return " \n" . $pkey . " " . trim($this->__query[$qkey]['QUICK_' . $pkey]);
		}
		if (!isset($this->__query[$qkey][$pkey])) { return ''; }
		$data = $this->__query[$qkey][$pkey];
		if (!is_array($data) || count($data) == 0) { return ''; }
		$sql = "$pkey ";
		$i = 0;
		foreach ($data as $data_parts) {
			if (is_array($data_parts) && isset($data_parts[0]['LEFT'])) {
				if ($i > 0) { $sql .= " OR "; }
				$sql .= "(";
				$p = 0;
				foreach ($data_parts as $part) {
					if (in_array($part['OPERATOR'], $this->__where_operators)) {
						if ($p > 0) { $sql .= " AND "; }
						$op = $part['OPERATOR'];
						$left = $this->database_object->escapeNames($part['LEFT']);
						if (is_null($part['RIGHT']) || (is_string($part['RIGHT']) && $part['RIGHT'] == 'NULL')) {
							$righ = "NULL";
						} else {
							if (is_string($part['RIGHT']) && strtoupper($part['RIGHT']) == '\NULL') {
								$part['RIGHT'] = substr($part['RIGHT'], 1);
							}
							$righ = "'" . $this->database_object->escapeStr($part['RIGHT']) . "'";
						}
						$sql .= "$left $op $righ";
						$p++;
					}
				}
				$i++;
				$sql .= ")";
			}
		}
		return " \n" . $sql;
	}

	# ###########################################################################

	/**
	* Internal method for compiling SQL ORDER BY and GROUP BY statements
	*
	* @param integer $mode 0 (zero) for ORDER BY, anything else for  GROUP BY
	* @param string $qkey
	* @return string SQL
	*/
	protected function __compile_order_group($mode = 0, $qkey = null)
	{
		if (!$this->isReady()) { return false; }
		// Get the query base statement
		if ($qkey == null) { $qkey = $this->__query_key; }
		if (!isset($this->__query[$qkey])) { return ''; }
		$pkey = ($mode == 0) ? 'ORDER_BY' : 'GROUP_BY';
		if (!isset($this->__query[$qkey][$pkey])) { return ''; }
		$data = $this->__query[$qkey][$pkey];
		if (!is_array($data) || count($data) == 0) { return ''; }
		$sql = '';
		foreach ($data as $col => $descending) {
			if ($sql == '') { $sql = str_replace("_", " ", $pkey) . " "; }
			else { $sql .= ", "; }
			$sql .= $this->database_object->escapeNames($col);
			$sql .= ($descending) ? " DESC" : "ASC";
		}
		return " \n" . $sql;
	}

	# ###########################################################################

	/**
	* Internal method for compiling SQL LIMIT statements
	*
	* @param string $qkey
	* @return string SQL
	*/
	protected function __compile_limit($qkey = null)
	{
		// Get the query base statement
		if ($qkey == null) { $qkey = $this->__query_key; }
		if (!isset($this->__query[$qkey]['LIMIT'])) { return ''; }

		$data = $this->__query[$qkey]['LIMIT'];
		if (!is_array($data) || count($data) != 2) { return ''; }
		if (!is_int($data[0])) { $data[0] = intval($data[0]); }
		$sql = '';

		if (!is_null($data[1]) && is_int($data[1]) && $data[1] > 0) {
			$sql = " \nLIMIT " . $data[0] . "," . $data[1];
		} else if ($data[0] > 0) {
			$sql = " \nLIMIT " . $data[0];
		}
		return $sql;
	}

	# ###########################################################################

	/**
	* Compiles the SQL code
	*
	* @return string SQL
	*/
	public function execute()
	{
		switch ($this->__query_key) {
			case 'SELECT': return $this->__compile_select();
			case 'INSERT': return $this->__compile_insert();
			case 'UPDATE': return $this->__compile_update();
			case 'DELETE': return $this->__compile_delete();
			default:
				if (strstr($this->__query_key, 'UNION_SELECT_') && isset($this->__query['SELECT'])) {
					$sql = $this->__compile_select();
					for ($union = 1; $union <= $this->__union_index; $union++) {
						if (isset($this->__query['UNION_SELECT_' . $union])) {
							$sql .= " \nUNION \n" . $this->__compile_select('UNION_SELECT_' . $union);
						}
					}
					return $sql;
				}
		}
	}

	# ###########################################################################

}
